package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.Http;

public interface ServiceChannelManagerRepository extends
		JpaRepository<Http, String> {
	@Query(nativeQuery = true, value = "SELECT    "
			+ " (SELECT GROUP_CONCAT(t1.IF_TOKEN_ID) AS IF_TOKEN_ID FROM t_esb_bu_if_token t1 WHERE t1.APP_KEY=t.APP_KEY) AS id,  "  
			+ " ( "
			+ " 	SELECT dbf0.field_name  "
			+ " 	FROM t_cook_bu_field_system fs0,  "
			+ " 	t_cook_db_field dbf0  "
			+ " 	WHERE  "
			+ " 	fs0.field_id=dbf0.field_id  "
			+ " 	AND fs0.field_system_id=t.IF_CALL_SYSTEM_ID) AS callFieldName,   "
			+ " t.IF_CALL_SYSTEM_ID AS callSystemId,    "
			+ " ( "
			+ " 	SELECT fs.system_name  "
			+ " 	FROM t_cook_bu_field_system fs  "
			+ " 	WHERE fs.field_system_id=t.IF_CALL_SYSTEM_ID) AS callSystemName,    "
			+ " ( "
			+ " 	SELECT dbf01.field_name  "
			+ " 	FROM t_cook_bu_field_system fs01, t_cook_db_field dbf01  "
			+ " 	WHERE fs01.field_id=dbf01.field_id AND fs01.field_system_id=t.IF_PUB_SYSTEM_ID) AS pubFieldName,  " 
			+ " t.IF_PUB_SYSTEM_ID pubSystemId,    "
			+ " ( "
			+ " 	SELECT fs2.system_name  "
			+ " 	FROM t_cook_bu_field_system fs2  "
			+ " 	WHERE fs2.field_system_id= t.IF_PUB_SYSTEM_ID) AS pubSystemName,  "  
			+ " (SELECT GROUP_CONCAT(t1.IF_ID) AS IF_TOKEN_ID FROM t_esb_bu_if_token t1 WHERE t1.APP_KEY=t.APP_KEY) AS ifId,  "
			+ " (SELECT GROUP_CONCAT(t1.IF_NAME) AS IF_TOKEN_ID FROM t_esb_bu_if_token t1 WHERE t1.APP_KEY=t.APP_KEY) AS ifName,  "
			+ " t.APP_KEY AS appKey,    "
			+ " t.IP_WHITELIST whiteList,  "
			+ " t.CREATED_DATE AS createDate,    "
			+ " t.IS_ENABLE isEnable,  "
			+ " t.IF_TOKEN_STATUS STATUS,  "
			+ " t.KEY_INVALID_DATE invalidDate    "
			+ " FROM(   "
			+ " 	SELECT    "
			+ " 	t0.IF_CALL_SYSTEM_ID,t0.IF_PUB_SYSTEM_ID,   "
			+ " 	t0.APP_KEY, "
			+ " 	t0.IP_WHITELIST, "
			+ " 	t0.AK_APPLY_DATE, "
			+ " 	t0.IS_ENABLE, "
			+ " 	t0.IF_TOKEN_STATUS, "
			+ " 	t0.CREATED_DATE,  "
			+ " 	t0.KEY_INVALID_DATE   "
			+ " 	FROM t_esb_bu_if_token t0  "  
			+ " 	WHERE   "
			+ " 	t0.IS_ENABLE='1' "
			+ " 	AND t0.APP_KEY IS NOT NULL   "
			+ " 	AND EXISTS( "
			+ " 		SELECT  "
			+ " 		uu2.FIELD_SYSTEM_ID  "
			+ " 		FROM t_cook_bu_user_system uu2  "
			+ " 		WHERE  "
			+ " 		uu2.FIELD_SYSTEM_ID=t0.IF_CALL_SYSTEM_ID  "
			+ " 		AND uu2.USER_ID=:userId)   "
			+ " 	GROUP BY t0.APP_KEY   "
			+ " ) AS t  ORDER BY CREATED_DATE DESC  LIMIT :pageNumber,:pageSize ")
	List<Object[]> findServiceChannelInfo(@Param("userId") String userId,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = ""
			+ " SELECT COUNT(V0.APP_KEY) "   
			+ " FROM(  " 
			+ "	SELECT  "
			+ "	DISTINCT t0.APP_KEY "
			+ "	FROM t_esb_bu_if_token t0   " 
			+ "	WHERE "
			+ "	t0.IS_ENABLE='1' "  
			+ "	AND t0.APP_KEY IS NOT NULL "  
			+ "	AND EXISTS( "
			+ "		SELECT " 
			+ "		uu2.FIELD_SYSTEM_ID "
			+ "		FROM t_cook_bu_user_system uu2 " 
			+ "		WHERE uu2.FIELD_SYSTEM_ID=t0.IF_CALL_SYSTEM_ID " 
			+ "		AND uu2.USER_ID=:userId "
			+ "		) "  	 
			+ ") AS V0")
	List<Object> findServiceChannelTotalNum(@Param("userId") String userId);

	@Query(nativeQuery = true, value = "SELECT if0.IF_HTTP_ID AS id, "
			+ " if0.IF_NAME AS ifName,  "
			+ " if0.IF_CODE AS ifCode  "
			+ " FROM t_esb_bu_if_http if0  "
			+ " WHERE  "
			+ " 	if0.IS_ENABLE='1' AND if0.AUDIT_STATUS='1'   "
			+ " 	AND if0.field_system_id =:pubSystemId "

			+ " 	AND NOT EXISTS( SELECT token0.IF_ID FROM t_esb_bu_if_token token0    "
			+ " 	WHERE token0.IS_ENABLE='1'  AND token0.IF_PUB_SYSTEM_ID =:pubSystemId  AND (''=:callSystemId OR token0.IF_CALL_SYSTEM_ID =:callSystemId) "
			+ " 	AND token0.IF_ID = if0.IF_HTTP_ID)    "

			+ " 	AND(if0.IS_PUBLIC='1'  "
			+ " 		OR EXISTS(SELECT * FROM t_cook_bu_user_system yy WHERE   yy.field_system_id=:pubSystemId AND yy.USER_ID=:userId)   "
			+ " 		OR EXISTS(SELECT * FROM t_esb_bu_if_visiblity vv WHERE   vv.IF_ID=if0.IF_HTTP_ID  "
			+ " 			AND EXISTS(SELECT * FROM t_cook_bu_user_system uu2 WHERE uu2.FIELD_SYSTEM_ID=vv.IF_VISIBLE_SYSTEM_ID AND uu2.USER_ID=:userId)))")
	List<Object[]> getAvailableInterface(
			@Param("pubSystemId") String pubSystemId,
			@Param("callSystemId") String callSystemId,
			@Param("userId") String userId);

	@Query(nativeQuery = true, value = "SELECT if0.IF_HTTP_ID AS id, "
			+ " if0.IF_NAME AS ifName,  "
			+ " if0.IF_CODE AS ifCode  "
			+ " FROM t_esb_bu_if_http if0  "
			+ " WHERE  "
			+ " 	if0.IS_ENABLE='1' AND if0.AUDIT_STATUS='1'   "
			+ " 	AND if0.field_system_id =:pubSystemId "

			+ " 	AND NOT EXISTS( SELECT token0.IF_ID FROM t_esb_bu_if_token token0    "
			+ " 	WHERE token0.IS_ENABLE='1'  AND token0.IF_PUB_SYSTEM_ID =:pubSystemId  AND (''=:callSystemId OR token0.IF_CALL_SYSTEM_ID =:callSystemId) "
			+ " 	AND token0.IF_ID = if0.IF_HTTP_ID)    "

			+ " 	AND(if0.IS_PUBLIC='1'  "
			+ " 		OR EXISTS(SELECT * FROM t_cook_bu_user_system yy WHERE   yy.field_system_id=:pubSystemId AND yy.USER_ID=:userId)   "
			+ " 		OR EXISTS(SELECT * FROM t_esb_bu_if_visiblity vv WHERE   vv.IF_ID=if0.IF_HTTP_ID  "
			+ " 			AND EXISTS(SELECT * FROM t_cook_bu_user_system uu2 WHERE uu2.FIELD_SYSTEM_ID=vv.IF_VISIBLE_SYSTEM_ID AND uu2.USER_ID=:userId)))"
			+ " LIMIT :pageNumber,:pageSize ")
	List<Object[]> getAvailableInterface(
			@Param("pubSystemId") String pubSystemId,
			@Param("callSystemId") String callSystemId,
			@Param("userId") String userId,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT COUNT(if0.IF_HTTP_ID) FROM t_esb_bu_if_http if0  "
			+ " WHERE NOT EXISTS( SELECT token0.IF_ID FROM t_esb_bu_if_token token0  "
			+ " WHERE  token0.IS_ENABLE='1' "
			+ " AND token0.IF_PUB_SYSTEM_ID =:pubSystemId "
			+ " AND token0.IF_CALL_SYSTEM_ID =:callSystemId  "
			+ " AND token0.IF_ID = if0.IF_HTTP_ID)  "
			+ " AND (''=:isEnable OR if0.IS_ENABLE=:isEnable) "
			+ " AND if0.field_system_id =:pubSystemId "
			+ " AND( EXISTS(SELECT * FROM t_cook_bu_user_system yy WHERE  "
			+ " yy.field_system_id=:pubSystemId AND yy.USER_ID=:userId) "
			+ " OR EXISTS(SELECT * FROM t_esb_bu_if_visiblity vv WHERE  "
			+ " vv.IF_ID=if0.IF_HTTP_ID AND EXISTS(SELECT * FROM t_cook_bu_user_system uu2 WHERE uu2.FIELD_SYSTEM_ID=vv.IF_VISIBLE_SYSTEM_ID AND uu2.USER_ID=:userId)) "
			+ " OR if0.IS_PUBLIC=:isPublic" + "  )")
	List<Object> getAvailableInterfaceTotalNum(
			@Param("pubSystemId") String pubSystemId,
			@Param("callSystemId") String callSystemId,
			@Param("userId") String userId, @Param("isPublic") String isPublic,
			@Param("isEnable") String isEnable);

	@Query(nativeQuery = true, value = "SELECT token.IF_TOKEN_ID as id,token.IF_ID AS interfaceId, token.if_name AS interfaceName, token.if_code AS interfaceCode, "
			+ " token.app_key AS appKey, token.apply_user_code AS appUserCode,token.if_token_status AS status, "
			+ " token.is_enable AS isEnable ,token.CREATED_DATE AS createdDate,  "
			+ " token.IF_PUB_SYSTEM_ID AS pubSystemId, "
			+ " token.IF_PUB_SYSTEM_CODE AS pubSystemCode, "
			+ " (SELECT s0.SYSTEM_NAME FROM t_cook_bu_field_system s0 WHERE s0.FIELD_SYSTEM_ID = token.IF_PUB_SYSTEM_ID) AS pubSystemName, "
			+ " token.IF_CALL_SYSTEM_ID AS callSystemId, "
			+ " token.IF_CALL_SYSTEM_CODE AS callSystemCode, "
			+ " (SELECT s0.SYSTEM_NAME FROM t_cook_bu_field_system s0 WHERE s0.FIELD_SYSTEM_ID = token.IF_CALL_SYSTEM_ID) AS callSystemName,  "
			+ " token.KEY_INVALID_DATE AS keyInvalidDate "
			+ " FROM t_esb_bu_if_token token "
			+ " WHERE  "
			+ " token.IS_ENABLE='1' AND "
			+ " (''=:interfaceName OR token.IF_NAME LIKE :interfaceName) AND "
			+ " (''=:pubFieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=token.IF_PUB_SYSTEM_ID AND s.FIELD_ID=:pubFieldId)) AND "
			+ " (''=:callFieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=token.IF_CALL_SYSTEM_ID AND s.FIELD_ID=:callFieldId)) AND "
			+ " (''=:pubSystemId OR token.IF_PUB_SYSTEM_ID =:pubSystemId) AND "
			+ " (''=:callSystemId OR token.IF_CALL_SYSTEM_ID =:callSystemId) AND "
			+ " (''=:status OR token.IF_TOKEN_STATUS =:status) AND "
			+ " (''=:appUserCode OR token.APPLY_USER_CODE LIKE :appUserCode) AND "
			+ " (''=:isEnable OR token.IS_ENABLE =:isEnable) AND "
			+ " (''=:appStartDate OR token.CREATED_DATE >= :appStartDate) AND "
			+ " (''=:appEndDate OR token.CREATED_DATE <= :appEndDate) AND "
			+ " EXISTS(SELECT * FROM t_cook_bu_user_system uu2 WHERE uu2.FIELD_SYSTEM_ID=token.IF_PUB_SYSTEM_ID AND uu2.USER_ID=:userId) AND "
			+ " (''=:now OR token.KEY_INVALID_DATE <:now) AND "
			+ " (''=:expireDate OR token.KEY_INVALID_DATE >=:expireDate)  "
			+ " ORDER BY token.CREATED_DATE DESC  "
			+ " LIMIT :pageNumber,:pageSize ")
	List<Object[]> findInterfaceTokenInfos(
			@Param("interfaceName") String interfaceName,
			@Param("pubFieldId") String pubFieldId,
			@Param("callFieldId") String callFieldId,
			@Param("pubSystemId") String pubSystemId,
			@Param("callSystemId") String callSystemId,
			@Param("status") String status,
			@Param("appUserCode") String appUserCode,
			@Param("appStartDate") String appStartDate,
			@Param("appEndDate") String appEndDate,
			@Param("isEnable") String isEnable, @Param("userId") String userId,
			@Param("now") String now, @Param("expireDate") String expireDate,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT COUNT(token.IF_TOKEN_ID) FROM t_esb_bu_if_token token "
			+ " WHERE  "
			+ " token.IS_ENABLE='1' AND "
			+ " (''=:interfaceName OR token.IF_NAME LIKE :interfaceName) AND "
			+ " (''=:pubFieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=token.IF_PUB_SYSTEM_ID AND s.FIELD_ID=:pubFieldId)) AND "
			+ " (''=:callFieldId OR EXISTS(SELECT s.FIELD_ID FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=token.IF_CALL_SYSTEM_ID AND s.FIELD_ID=:callFieldId)) AND "
			+ " (''=:pubSystemId OR token.IF_PUB_SYSTEM_ID =:pubSystemId) AND "
			+ " (''=:callSystemId OR token.IF_CALL_SYSTEM_ID =:callSystemId) AND "
			+ " (''=:status OR token.IF_TOKEN_STATUS =:status) AND "
			+ " (''=:appUserCode OR token.APPLY_USER_CODE LIKE :appUserCode) AND "
			+ " (''=:isEnable OR token.IS_ENABLE =:isEnable) AND "
			+ " (''=:appStartDate OR token.CREATED_DATE >=:appStartDate) AND "
			+ " (''=:appEndDate OR token.CREATED_DATE <=:appEndDate) AND "
			+ " EXISTS(SELECT * FROM t_cook_bu_user_system uu2 WHERE uu2.FIELD_SYSTEM_ID=token.IF_PUB_SYSTEM_ID AND uu2.USER_ID=:userId) AND"
			+ " (''=:now OR token.KEY_INVALID_DATE <:now) AND  "
			+ " (''=:expireDate OR token.KEY_INVALID_DATE >=:expireDate)  ")
	List<Object> findInterfaceTokenTotalNum(
			@Param("interfaceName") String interfaceName,
			@Param("pubFieldId") String pubFieldId,
			@Param("callFieldId") String callFieldId,
			@Param("pubSystemId") String pubSystemId,
			@Param("callSystemId") String callSystemId,
			@Param("status") String status,
			@Param("appUserCode") String appUserCode,
			@Param("appStartDate") String appStartDate,
			@Param("appEndDate") String appEndDate,
			@Param("isEnable") String isEnable, @Param("userId") String userId,
			@Param("now") String now, @Param("expireDate") String expireDate);

	@Query(nativeQuery = true, value = "SELECT  "
			+ " v1.interfaceId AS interfaceId1, "
			+ " v1.systemId AS systemId, "
			+ " (SELECT f1.field_name FROM t_cook_bu_field_system s1  "
			+ " 	INNER JOIN t_cook_db_field f1 ON s1.field_id=f1.field_id WHERE v1.systemId=s1.field_system_id) AS fieldName, "
				
			+ " (SELECT s2.system_name FROM  t_cook_bu_field_system s2 WHERE v1.systemId=s2.field_system_id) AS systemName, "
					
			+ " v1.interfaceName AS interfaceName, "
			+ " (SELECT GROUP_CONCAT(s1.SYSTEM_NAME) AS systemId FROM t_esb_bu_if_visiblity vs1 INNER JOIN t_cook_bu_field_system s1  "
			+ " 		ON vs1.if_visible_system_id=s1.field_system_id WHERE v1.interfaceId=vs1.if_id "
			+ " ) AS visibleListName, "
			
			+ " (SELECT GROUP_CONCAT(vs1.if_visible_system_id) AS systemId FROM t_esb_bu_if_visiblity vs1 "
			+ " 		WHERE v1.interfaceId=vs1.if_id "
					+ " ) AS visibleListId "
			
			+ " FROM	 "	
			+ " 	(SELECT v0.IF_HTTP_ID AS interfaceId,v0.IF_NAME AS interfaceName, v0.FIELD_SYSTEM_ID AS systemId FROM t_esb_bu_if_http v0  "
			+ " 	WHERE   "
			+ " 	v0.IS_ENABLE='1' AND v0.IS_PUBLIC='0' AND "
			+ " 	(''=:fieldId OR EXISTS(  "
			+ " 		SELECT f0.field_id FROM  t_cook_bu_field_system s0  "
			+ " 		INNER JOIN t_cook_db_field f0 ON s0.field_id=f0.field_id WHERE s0.field_system_id=v0.field_system_id AND f0.field_id=:fieldId)) AND  "
			+ " 	(''=:systemId OR EXISTS(  "
			+ " 		SELECT s01.field_id FROM t_cook_bu_field_system s01  " 
			+ " 		WHERE v0.field_system_id=s01.field_system_id AND  s01.field_system_id=:systemId)) AND  "
			+ " 	(''=:interfaceName OR  v0.if_name LIKE :interfaceName) AND "
			+ "     EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE v0.field_system_id = tu.field_system_id AND tu.USER_ID =:userId)"
			+ "     LIMIT :pageNumber,:pageSize) AS v1")
	List<Object[]> findVisibleInfo(@Param("fieldId") String fieldId,
			@Param("systemId") String sytemId,
			@Param("interfaceName") String interfaceName,
			@Param("userId") String userId,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	@Query(nativeQuery = true, value = "SELECT COUNT(v0.IF_HTTP_ID) AS total FROM t_esb_bu_if_http v0 "
			+ " WHERE  "
			+ " v0.IS_ENABLE='1' AND v0.IS_PUBLIC='0' AND"
			+ " (''=:fieldId OR EXISTS( "
			+ " 	SELECT f0.field_id FROM  t_cook_bu_field_system s0  "
			+ " 	INNER JOIN t_cook_db_field f0 ON s0.field_id=f0.field_id WHERE s0.field_system_id=v0.field_system_id AND f0.field_id=:fieldId)) AND "
			+ " (''=:systemId OR EXISTS( "
			+ " SELECT s01.field_id from t_cook_bu_field_system s01  "
			+ "  WHERE v0.field_system_id=s01.field_system_id AND  s01.field_system_id=:systemId)) AND "
			+ " (''=:interfaceName OR  v0.if_name LIKE :interfaceName) AND "
			+ "     EXISTS(SELECT tu.field_system_id FROM t_cook_bu_user_system tu WHERE v0.field_system_id = tu.field_system_id AND tu.USER_ID =:userId)")
	List<Object> findVisibleTotalNum(@Param("fieldId") String fieldId,
			@Param("systemId") String sytemId,
			@Param("interfaceName") String interfaceName,
			@Param("userId") String userId);

}
